Today in the Metis bootcamp we learnt SQL. I read one book Learning SQL some months ago, but I still need more practice on it. So I take some notes here to remind myself its basic syntax. This practice based on this website.
Some resources:
The typical SQL database structure:
What is the name of the customer who has the most orders?
select c.CustomerID ,COUNT(*) AS count
from orders o
join Customers c
on c.CustomerID=o.CustomerID
group by o.customerID
order by count DESC;
What supplier has the highest average product price?
select s.SupplierName, avg(price)
from products p
join suppliers s on p.supplierID=s.supplierID
group by 1
How many different countries are there customers from? (Hint: Consider DISTINCT.)
select count(distinct country) as total_counties from customers
What category appears in the most orders?
select categoryName, count(distinct orderID)
from orderDetails o
join products p on p.productID = o.ProductID
join categories c on c.categoryID=p.categoryID
group by 1
order by 2 DESC
limit 1
What was the total cost for each order?
select orderID, sum(product_price)
from (select orderID, quantity*price as product_price
from orderdetails o
join products p on p.productID=o.productID)
group by 1
What employee made the most sales (by total cost)?
SELECT e.FirstName, e.LastName, e.employeeID, sum(product_price)
from
(select orderID, sum(quantity*price) as product_price
from orderdetails as o
join products as p on p.productID=o.productID
group by 1) x
join orders o on x.orderID=o.orderID
join employees e on e.employeeID=o.employeeID
group by 1,2
order by 3 desc
What Employees have BS degrees? (Hint: Look at the LIKE operator.)
SELECT firstName||" "||LastName as name from employees
where Notes like '% BS %'
What supplier of three or more products has the highest average product price? (Hint: Look at the HAVING operator.)
select supplierName, avg_price from
(select supplierID, avg(price) as avg_price
from products
group by 1
having count(*) >2) x
join suppliers s on x.supplierID=s.supplierID
order by avg_price DESC
Remark:
-
||" "||: Combine two columns.